﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace Utility4Net.Data
{
    public class SQLiteHelper
    {
        /// <summary>
        /// ConnectionString样例：Datasource=Test.db3;Pooling=true;FailIfMissing=false
        /// </summary>
        public static string ConnectionString { get; set; }

        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params object[] p)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Parameters.Clear();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 30;
            if (p != null)
            {
                foreach (object parm in p)
                    cmd.Parameters.AddWithValue(string.Empty, parm);
            }
        }

        public static DataSet ExecuteQuery(string cmdText, params object[] p)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    DataSet ds = new DataSet();
                    PrepareCommand(command, conn, cmdText, p);
                    SQLiteDataAdapter da = new SQLiteDataAdapter(command);
                    da.Fill(ds);
                    return ds;
                }
            }
        }

        public static int ExecuteNonQuery(string cmdText, params object[] p)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    PrepareCommand(command, conn, cmdText, p);
                    return command.ExecuteNonQuery();
                }
            }
        }

        public static SQLiteDataReader ExecuteReader(string cmdText, params object[] p)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    PrepareCommand(command, conn, cmdText, p);
                    return command.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
        }

        public static object ExecuteScalar(string cmdText, params object[] p)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    PrepareCommand(command, conn, cmdText, p);
                    return command.ExecuteScalar();
                }
            }
        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataSet(string SQLString)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SQLite.SQLiteException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }

        public static DataTable ExecuteDataTable(string sqlScript)
        {
            DataTable dt = new DataTable();
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                conn.Open();
                using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(sqlScript, conn))
                {
                    dataAdapter.Fill(dt);
                }
                conn.Close();
            }
            return dt;
        }

        public static int ExecuteNonQuery(string sqlScript)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                conn.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(sqlScript, conn))
                {
                    return cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }
        public static void CreateFile(string dbPath)
        {
            SQLiteConnection.CreateFile(dbPath);
        }
        /// <summary>   
        /// 查询数据库中的所有数据类型信息   
        /// </summary>   
        /// <returns>DataTable</returns>   
        public static DataTable GetSchema()
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();
                DataTable data = connection.GetSchema("TABLES");
                connection.Close();
                //foreach (DataColumn column in data.Columns)   
                //{   
                //    Console.WriteLine(column.ColumnName);   
                //}   
                return data;
            }
        }
        public static bool TableExist(string tableName)
        {
            DataTable dt = GetSchema();
            return dt.Select(string.Format("Table_Name = '{0}'", tableName)).Length > 0;
        }
        /// <summary>   
        /// 判断SQLite数据库表是否存在  
        /// </summary>   
        /// <param name="dbPath">要创建的SQLite数据库文件路径</param>
        /// <returns>bool</returns>
        public static bool IsTableExist(string tableName)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {

                    command.CommandText = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='" + tableName + "'";
                    int iaaa = Convert.ToInt32(command.ExecuteScalar());
                    if (Convert.ToInt32(command.ExecuteScalar()) == 0)
                    {
                        return false;
                    }
                    else
                    {
                        return true;
                    }
                }
            }
        }
    }
}
